-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
USE [WarehouseManagement]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		TuyenVD
-- Create date: 3/16/2011
-- Description:	This stored procedure use to update an Employee record
-- =============================================
ALTER PROCEDURE [UpdateEmployee]
(
	@EmployeeID INT
	, @FullName NVARCHAR(MAX)
	, @DateOfBirth DATE
	, @IdentityCardNumber NVARCHAR(9)
	, @Gender NVARCHAR(10)
	, @JoinedDate DATE
	, @Address NVARCHAR(MAX)
	, @Email VARCHAR(50) = NULL
	, @PhoneNumber VARCHAR(20) = NULL
	, @MobileNumber VARCHAR(20) = NULL
	, @Description NVARCHAR(MAX) = NULL
	, @EmploymentTypeID INT
	, @RoleID INT
	, @Username NVARCHAR(MAX)
	, @AuthorizationIDList NVARCHAR(MAX) = NULL
	, @Result BIT OUT
)
AS
BEGIN
	--
	SET @Result = 0

	IF (NOT EXISTS (SELECT 1 FROM [Employee] WHERE [Username] = @Username AND [EmployeeID] <> @EmployeeID))
	BEGIN
		UPDATE [Employee]
		SET
			[FullName] = @FullName
			, [DateOfBirth] = @DateOfBirth
			, [IdentityCardNumber] = @IdentityCardNumber
			, [Gender] = @Gender
			, [JoinedDate] = @JoinedDate
			, [Email] = @Email
			, [Address] = @Address
			, [PhoneNumber] = @PhoneNumber
			, [MobileNumber] = @MobileNumber
			, [Description] = @Description
			, [EmploymentTypeID] = @EmploymentTypeID
			, [RoleID] = @RoleID
			, [Username] = @Username
		WHERE
			[EmployeeID] = @EmployeeID

		IF (EXISTS (
					SELECT 1 FROM [Employee] 
					WHERE 
						[FullName] = @FullName
						AND [DateOfBirth] = @DateOfBirth
						AND [IdentityCardNumber] = @IdentityCardNumber
						AND [Gender] = @Gender
						AND [JoinedDate] = @JoinedDate
						AND ([Email] = @Email OR [Email] IS NULL)
						AND [Address] = @Address
						AND ([PhoneNumber] = @PhoneNumber OR [PhoneNumber] IS NULL)
						AND ([MobileNumber] = @MobileNumber OR [MobileNumber] IS NULL)
						AND ([Description] = @Description OR [Description] IS NULL)
						AND [EmploymentTypeID] = @EmploymentTypeID
						AND [RoleID] = @RoleID
						AND [Username] = @Username
						AND [EmployeeID] = @EmployeeID)
						)
		BEGIN
			DELETE FROM [AccountAuthorization]
			WHERE [EmployeeID] = @EmployeeID
			------------------------------
			DECLARE @XmlPointer INT
	 
			EXEC sp_xml_preparedocument @XmlPointer OUTPUT, @AuthorizationIDList

			INSERT INTO 
					AccountAuthorization(EmployeeID, AuthorizationID)
			SELECT 
					@EmployeeID
					, Data.ID
			FROM OpenXml(@XmlPointer, 'Data/Item', 2)
			WITH (ID INT) AS Data
			 
			INNER JOIN --check of the valid ID from the list
					[Authorization] a
			ON
					a.[AuthorizationID] = Data.[ID]
					
			EXEC sp_xml_removedocument @XmlPointer	
			------------------------------
			SET @Result = 1
		END
	END
				
END

/*
USE WarehouseManagement
GO

DECLARE @Result BIT = 0

EXEC [UpdateEmployee] @EmployeeID = 13,
	@FullName = 'G Admin', 
	@DateOfBirth = '1989 - 03 -31',
	@IdentityCardNumber = '012584296', 
	@Email = 'anhdq003174@gmail.com',  
	@Address = 'Lieu Giai - Ba Dinh - Hanoi 4',
	@PhoneNumber = '01675362022',
	@EmploymentTypeID = 1,
	@RoleID = 1,
	@Description = 'ksdgf oigygqryg',
	@Username = 'gadmin',
	@Gender = 'Nam',
	@JoinedDate = '2011-01-01',
	@AuthorizationIDList = '10, 4, 7, 1, 13, 16, 19, 20, 21, 22, 23, 24, 25, 26, 27',
	@Result = @Result OUT

SELECT @Result
SELECT * FROM [Employee] WHERE EmployeeID = 13
SELECT * FROM [AccountAuthorization] WHERE EmployeeID = 13
*/